package org.flower.dao.impl;
import org.flower.dao.UserDao;
import org.flower.entity.CommodityDetails;
import org.flower.entity.PageBean;
import org.flower.entity.User;
import org.flower.utils.BaseDao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;


/**
 * @author Aizen
 */
public class UserDaoImpl extends BaseDao implements UserDao {
    @Override
    public List<User> getAll() {
        List<User> list = new ArrayList<User>();
        String sql = "SELECT * FROM USER";
        ResultSet rs = executeQuery(sql);
        try {
            while (rs.next()){
                list.add(new User(rs.getInt("id"),rs.getString("name"),rs.getString("pwd"),rs.getString("sex"),rs.getString("phone"),rs.getDate("borndate"),rs.getString("photo"),rs.getString("email"),rs.getBoolean("state"),rs.getDate("LoginDate")));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public boolean updataStateById(int uid) {
        String sql = "UPDATE USER SET state=NOT state WHERE id = ?";
        return executeUpdate(sql,uid);
    }

    @Override
    public List<User> getUsers(User user, PageBean pageBean) {
        List<User> list = new ArrayList<User>();
        String sql = "SELECT * FROM USER " + addString(user);
        sql+=" LIMIT "+pageBean.getStart()+","+pageBean.getRows();
        ResultSet rs = executeQuery(sql);
        try {
            while (rs.next()){
                list.add(new User(rs.getInt("id"),rs.getString("name"),rs.getString("pwd"),rs.getString("sex"),rs.getString("phone"),rs.getDate("borndate"),rs.getString("photo"),rs.getString("email"),rs.getBoolean("state"),rs.getDate("LoginDate")));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public int getCount(User user) {
        int result = -1;
        String sql = "SELECT COUNT(1) c FROM USER" + addString(user);
        ResultSet rs = executeQuery(sql);
        try {
            if(rs.next()){
                result = rs.getInt("c");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return result;
    }

    @Override
    public int getDayAddUser() {
        int dayAddUserNumber=-1;
        String sql = "SELECT count(1) c FROM USER WHERE TO_DAYS(LoginDate) = TO_DAYS(NOW())";
        ResultSet rs = executeQuery(sql);
        try {
            if(rs.next()){
                dayAddUserNumber = rs.getInt("c");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return dayAddUserNumber;
    }

    @Override
    public List<Integer> getZhouDate() {
        List<Integer> list = new ArrayList<>();
        String sql = "SELECT COUNT(1) c FROM `user` WHERE TO_DAYS(logindate) = TO_DAYS(SUBDATE(NOW(), ?))";
        for (int i = 0; i <= 7; i++) {
            ResultSet rs = executeQuery(sql, i);
            try {
                if(rs.next()){
                    list.add(rs.getInt("c"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                try {
                    closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public int getAllUserCount() {
        int result = -1;
        String sql = "select count(1) c from user";
        ResultSet rs = executeQuery(sql);
        try {
            if (rs.next()){
                result = rs.getInt("c");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 模糊查询拼接字符串
      */
    private String addString(User u){
        String sql = " where 1=1 ";
        if(u.getName()!=null&&!u.getName().equals("")){
            sql+=" and name like '%"+u.getName()+"%' ";
        }
        if(u.getSex()!=null&&!u.getSex().equals("")){
            sql+=" and sex='"+u.getSex()+"' ";
        }
        if(u.getPhone()!=null&&!u.getPhone().equals("")){
            sql+=" and phone like '%"+u.getPhone()+"%' ";
        }
        if(u.getLoginDate()!=null&&!u.getLoginDate().equals("")){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql+=" and LoginDate like '%"+sdf.format(u.getLoginDate())+"%' ";
        }
        if(!u.getStateStr().equals("全部")){
            sql+=" and state="+u.isState();
        }
        return sql;
    }

    /**
     *根据用户编号获取用户信息
     * @param id
     * @return
     */
    @Override
    public User getByid(int id) {
        String sql="select * from user where id=?";
        ResultSet rs=super.executeQuery(sql,id);
        User userinfo= null;
        try {
            while(rs.next()){
                userinfo = new User(rs.getInt("id"),rs.getString("name"),rs.getString("pwd"),rs.getString("sex"),rs.getString("phone"),rs.getDate("borndate"),
                        rs.getString("photo"),rs.getString("email"),rs.getBoolean("state"),rs.getDate("LoginDate"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                closeAll(rs, rs.getStatement(), rs.getStatement().getConnection());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return userinfo;
    }

    /**
     * 用户注册
     *
     * @param phone 电话
     * @param pwd   密码
     * @return boolean 是否成功
     */
    @Override
    public boolean userLogin(String phone, String pwd) {
        //默认图片
        String photo = "image/user/userDefault.jpg";
        String sql = "insert into `user`(`name`,pwd,phone,photo,loginDate) values(?,?,?,?,NOW())";
        return super.executeUpdate(sql,phone,pwd,phone,photo);
    }

    @Override
    public boolean checkPhone(String phone) {
        String sql = "select COUNT(1) from `user` where phone=?";
        ResultSet rs = super.executeQuery(sql, phone);
        int row = 0;
        try {
            if(rs.next()){
                row = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                super.closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return row==0;
    }
    /**
     * 登录账号密码是否正确
     */
    @Override
    public User login(String phone, String pwd) {
        String sql="select * from `user` where phone=? and pwd=?";
        ResultSet rs=super.executeQuery(sql,phone,pwd);
        User u = null;
        try {
            if(rs.next()){
                u = new User(rs.getInt("id"),rs.getString("name"),
                        rs.getString("pwd"),rs.getString("sex"),
                        rs.getString("phone"),rs.getDate("borndate"),
                        rs.getString("photo"),rs.getString("email"),
                        rs.getBoolean("state"),rs.getDate("LoginDate"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                super.closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return u;
    }

    @Override
    public boolean UpdateById(User user) {
        String sql="update `user` set name=?,sex=?,borndate=?,photo=? where id=?";
        return executeUpdate(sql,user.getName(),user.getSex(),user.getDateBorda(),user.getPhoto(),user.getId());
    }

    @Override
    public List<CommodityDetails> getTop() {
        String sql="select * from `commoditydetails` limit 8";
        List<CommodityDetails> list = new ArrayList<>();
        ResultSet rs =executeQuery(sql);
        try {
            while (rs.next()){
                CommodityDetails c = new CommodityDetails(
                        rs.getInt("id"),
                        rs.getString("ComName"),
                        rs.getString("Content"),
                        rs.getInt("outNum"),
                        rs.getDouble("outPrice"),
                        rs.getDouble("marketPrice"),
                        rs.getString("MaterialScience"),
                        rs.getString("packing"),
                        rs.getString("Distribution"),
                        rs.getString("comImage"),
                        rs.getString("detailsImage"),
                        rs.getInt("comShowId"),
                        rs.getInt("ClassId"),
                        rs.getInt("stockd"),
                        rs.getDate("insertDate"));
                String ress[]=c.getComImage().split(",");
                c.setComImage(ress[0]);
                list.add(c);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs, rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }

    @Override
    public boolean UpdatePwd(User user) {
        String sql="update `user` set pwd=? where id=?";
        return executeUpdate(sql,user.getPwd(),user.getId());
    }

    @Override
    public boolean findUserState(int userId) {
        boolean state = false;
        String sql = "SELECT state FROM `user` WHERE id = ?";
        ResultSet rs = executeQuery(sql, userId);
        try {
            if(rs.next()){
                state = rs.getBoolean(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs, rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return state;
    }

}
